package aisp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import wrappers.DataType;
import wrappers.Entity;
import wrappers.Link;
import wrappers.Property;

public class DataProvider {

	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	
	public ArrayList<Entity> entity = new ArrayList<Entity>();
	public ArrayList<Link> link = new ArrayList<Link>();
	public ArrayList<DataType> dataType = new ArrayList<DataType>();
	public ArrayList<Property> property = new ArrayList<Property>();
	public String[] multType = {"one-to-one", "one-to-many","many-to-one","many-to-many"};
	

	public DataProvider() {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
			.getConnection("jdbc:mysql://localhost/test?"
					+ "user=root&password=root");
			loadEntities();
			loadProperties();
			loadDataTypes();
			loadLinks();
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {

		}
	}
	
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}
	
	private void loadEntities(){
		// Statements allow to issue SQL queries to the database
		entity.clear();
		try {
			statement = connect.createStatement();
			resultSet = statement.executeQuery("select * from entity");
			int tempEntityId = 1;
			String tempEntityName = "";

			while (resultSet.next()) {
				tempEntityId = resultSet.getInt("idobjects");
				tempEntityName = resultSet.getString("name");
				entity.add(new Entity(tempEntityId, tempEntityName));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		entity.toString();
	}
	
	private void loadProperties(){
		// Statements allow to issue SQL queries to the database
		property.clear();
		try {
			statement = connect.createStatement();
			resultSet = statement.executeQuery("select * from property");
			int tempEntityId = 1;
			String tempName = "";
			int tempPropertyId = 1;
			int tempType = 1;

			while (resultSet.next()) {
				tempEntityId = resultSet.getInt("idEntity");
				tempName = resultSet.getString("name");
				tempPropertyId = resultSet.getInt("idproperty");
				tempType = resultSet.getInt("type");
				property.add(new Property(tempPropertyId, tempEntityId, tempName, tempType));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private void loadDataTypes(){
		// Statements allow to issue SQL queries to the database
		dataType.clear();
		try {
			statement = connect.createStatement();
			resultSet = statement.executeQuery("select * from types");
			int tempEntityId = 1;
			String tempSQL = "";
			String tempERD = "";

			while (resultSet.next()) {
				tempEntityId = resultSet.getInt("idtypes");
				tempSQL = resultSet.getString("nameSQL");
				tempERD = resultSet.getString("nameERD");
				dataType.add(new DataType(tempEntityId, tempSQL, tempERD));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private void loadLinks(){
		// Statements allow to issue SQL queries to the database
		link.clear();
		try {
			statement = connect.createStatement();
			resultSet = statement.executeQuery("select * from link");
			int tempLinkId = 1;
			int tempSourceId = 1;
			int tempTargetId = 1;
			int tempMultSource = 1;
			int tempMultTarget = 1;

			while (resultSet.next()) {
				tempLinkId = resultSet.getInt("idlink");
				tempSourceId = resultSet.getInt("idSource");
				tempTargetId = resultSet.getInt("idTarget");
				tempMultSource = resultSet.getInt("multiplicitySource");
				tempMultTarget = resultSet.getInt("multiplicityTarget");
				link.add(new Link(tempLinkId, tempSourceId, tempTargetId, tempMultSource, tempMultTarget));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void addLink(int source, int target, int type){
		int second = type/2;
		int first = type%2;
		try {
			if(source == target){
				return;
			}
			boolean exists = false;
			resultSet = statement.executeQuery("select * from link where (idSource=" + source + " and idTarget=" + target + ")"
				+ " or ( idTarget=" + source + " and idSource=" + target + ")");
			while (resultSet.next()) {
				exists = true;
			}			
			if(exists){
				System.out.println("Such link already exists");
				return;
			}		
			preparedStatement = connect
			.prepareStatement("insert into link  (idSource, idTarget, multiplicitySource, multiplicityTarget)  values " +
					"("+source+", "+target+", "+first+", "+second+")");

		System.out.println("insert into link  (idSource, idTarget, multiplicitySource, multiplicityTarget)  values " +
				"("+source+", "+target+", "+first+", "+second+")");
		preparedStatement.execute();
		loadLinks();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void editMultiplicity(int id, int type){
		int second = type/2;
		int first = type%2;
		try {
			preparedStatement = connect
			.prepareStatement("UPDATE link SET multiplicitySource= "+first+", multiplicityTarget= " +
					second+" WHERE idlink=\""+id+"\";");

		System.out.println("UPDATE link SET multiplicitySource= "+first+", multiplicityTarget= " +
				second+" WHERE idlink=\""+id+"\";");
		preparedStatement.execute();
		loadEntities();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void deleteLink(int linkId){
		try {
			preparedStatement = connect
			.prepareStatement("DELETE FROM link WHERE idlink=\""+linkId+"\";");

			System.out.println("DELETE FROM link WHERE idlink=\""+linkId+"\";");
		preparedStatement.execute();
		loadLinks();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void addEntity(String name){
		try {
			name = name.replaceAll(" ", "_");
			boolean exists = false;
			resultSet = statement.executeQuery("select * from entity where name=\"" + name + "\"");
			while (resultSet.next()) {
				exists = true;
			}			
			if(exists){
				System.out.println("Such entity already exists (\""+name+"\")");
				return;
			}
			preparedStatement = connect
			.prepareStatement("insert into entity (name) values (\""+name+"\")");

		System.out.println("insert into entity (name) values (\""+name+"\")");
		preparedStatement.execute();
		loadEntities();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void editEntity(int id, String name){
		try {
			name = name.replaceAll(" ", "_");
			preparedStatement = connect
			.prepareStatement("UPDATE name SET \""+name+"\" WHERE idobjects=\""+id+"\";");

		System.out.println("UPDATE entity SET name="+name+" WHERE idobjects=\""+id+"\";");
		preparedStatement.execute();
		loadEntities();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void deleteEntity(int id){
		try {
			preparedStatement = connect
			.prepareStatement("DELETE FROM entity WHERE idobjects=\""+id+"\";");

		System.out.println("DELETE FROM entity WHERE idobjects=\""+id+"\";");
		preparedStatement.execute();
		
		preparedStatement = connect
		.prepareStatement("DELETE FROM link WHERE idsource=\""+id+"\" or idtarget=\""+id+"\";");

		System.out.println("DELETE FROM link WHERE idsource=\""+id+"\" or idtarget=\""+id+"\";");
		preparedStatement.execute();
		
		preparedStatement = connect
		.prepareStatement("DELETE FROM property WHERE idEntity=\""+id+"\";");

	System.out.println("DELETE FROM property WHERE idEntity=\""+id+"\";");
	preparedStatement.execute();
		
		loadEntities();
		loadLinks();
		loadProperties();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public void addProperty(int idEntity, String name, int type){
		try {
			name = name.replaceAll(" ", "_");
			
			boolean exists = false;
			System.out.println("select * from property where name=\"" + name + "\" and idEntity=" + idEntity);
			resultSet = statement.executeQuery("select * from property where name=\"" + name + "\" and idEntity=" + idEntity);
			while (resultSet.next()) {
				exists = true;
			}			
			if(exists){
				System.out.println("Such property already exists for chosen entity");
				return;
			}
			preparedStatement = connect
			.prepareStatement("insert into property (idEntity, name, type) values ("+idEntity+",\""+name+"\","+type+")");

		System.out.println("insert into property (idEntity, name, type) values ("+idEntity+",\""+name+"\","+type+")");
		preparedStatement.execute();
		loadProperties();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void editProperty(int id, int type){
		try {
			
			preparedStatement = connect
			.prepareStatement("UPDATE property SET type= \""+type+"\" WHERE idproperty=\""+id+"\";");

		System.out.println("UPDATE property SET type="+type+" WHERE idproperty=\""+id+"\";");
		preparedStatement.execute();
		loadProperties();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void deleteProperty(int id){
		try {
			preparedStatement = connect
			.prepareStatement("DELETE FROM property WHERE idproperty=\""+id+"\";");

		System.out.println("DELETE FROM property WHERE idproperty=\""+id+"\";");
		preparedStatement.execute();
		loadProperties();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
}
